#!/bin/bash

host='10.61.13.96'
dbname='cflab'
charsetparam='--default-character-set=utf8'

validviewtimelength=300

workingDir='/cmsdata/cflab'
if [ ! -e $workingDir ]
then
    mkdir -p $workingDir
fi
echo "[info] checking data integrity..."
mysql -uroot -pmysql -h$host $charsetparam $dbname -e"select count(*), date_format(viewtime, '%Y-%m-%d') from programviewlog_last30days group by date_format(viewtime, '%Y-%m-%d');"

echo "[info] deleting viewlog of viewtimelength<$validviewtimelength..."
mysql -uroot -pmysql -h$host $charsetparam $dbname -e"drop table if exists tmp_programviewlog; create table tmp_programviewlog select userid, programid, viewtimelength, viewtime from programviewlog_last30days where viewtimelength>$validviewtimelength"

echo "[info] creating index on cleaned table..."
mysql -uroot -pmysql -h$host $charsetparam $dbname -e"create index programid on tmp_programviewlog(programid)"

echo '[info] generating viewlog table with type...'
mysql -uroot -pmysql -h$host $charsetparam $dbname -e"drop table if exists programviewlog; create table programviewlog select v.userid,v.programid,v.viewtimelength,v.viewtime,p.typeid from tmp_programviewlog v, program p where v.programid=p.programid"

mysql -uroot -pmysql -h$host $charsetparam $dbname -e"drop table if exists tmp_programviewlog"

echo "[info] table programviewlog contains viewtimelength>$validviewtimelength along with typeid!"

echo '[info] seperating programviewlog into types'
for t in {1..10}
do
    echo "[info] creating viewlog table for type $t"
    mysql -uroot -pmysql -h$host $charsetparam $dbname -e"drop table if exists programviewlog_type$t; create table prograiviewlog_type$t select * from programviewlog where typeid=$t"
done

echo '[info] dumping type 2 data into file...'
rm -f /tmp/movieviewlog_str
mysql -uroot -pmysql -h$host $charsetparam $dbname -e"select userid,programid,viewtimelength,viewtime from programviewlog where typeid=2 into outfile '/tmp/movieviewlog_str'"
mv /tmp/movieviewlog_str $workingDir/


echo '[info] converting string IDs into integer IDs...'
if [ ! -e $workingDir/yaml ]
then
    mkdir -p $workingDir/yaml
fi
ruby convert_str2int_rating.rb $workingDir movieviewlog_str

echo '[info] sorting file by uid, pid, viewtime'
sort -k1n -k2n -k4 -k5 $workingDir/movieviewlog_str_int > $workingDir/movieviewlog_str_int_sorted

echo '[info] aggregating sorted files...'
ruby aggregate_sorted.rb $workingDir movieviewlog_str_int_sorted

echo '[info] removing time column...'
awk -F"\t" '{printf $1"\t"$2"\t"$3"\n"}' $workingDir/movieviewlog_str_int > $workingDir/movieviewlog_str_int_-timestamp
awk -F"\t" '{printf $1"\t"$2"\t"$3"\n"}' $workingDir/movieviewlog_str_int_sorted_last > $workingDir/movieviewlog_str_int_sorted_last_-timestamp
awk -F"\t" '{printf $1"\t"$2"\t"$3"\n"}' $workingDir/movieviewlog_str_int_sorted_max > $workingDir/movieviewlog_str_int_sorted_max_-timestamp
awk -F"\t" '{printf $1"\t"$2"\t"$3"\n"}' $workingDir/movieviewlog_str_int_sorted_sum > $workingDir/movieviewlog_str_int_sorted_sum_-timestamp

echo '[info] adding timestamp column...'
awk -F'[\t -:]' '{ ts=mktime($4" "$5" "$6" "$7" "$8" "$9); printf $1 "\t" $2 "\t" $3 "\t" ts"000\n"}' $workingDir/movieviewlog_str_int > $workingDir/movieviewlog_str_int_+timestamp
awk -F'[\t -:]' '{ ts=mktime($4" "$5" "$6" "$7" "$8" "$9); printf $1 "\t" $2 "\t" $3 "\t" ts"000\n"}' $workingDir/movieviewlog_str_int_sorted_last >  $workingDir/movieviewlog_str_int_sorted_last_+timestamp
awk -F'[\t -:]' '{ ts=mktime($4" "$5" "$6" "$7" "$8" "$9); printf $1 "\t" $2 "\t" $3 "\t" ts"000\n"}' $workingDir/movieviewlog_str_int_sorted_max >  $workingDir/movieviewlog_str_int_sorted_max_+timestamp
awk -F'[\t -:]' '{ ts=mktime($4" "$5" "$6" "$7" "$8" "$9); printf $1 "\t" $2 "\t" $3 "\t" ts"000\n"}' $workingDir/movieviewlog_str_int_sorted_sum >  $workingDir/movieviewlog_str_int_sorted_sum_+timestamp

echo '[info] Task COMPLETED!' 


